/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.util.List;
import model.pojo.Deal;
import model.util.HibernateUtil;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;

/**
 *
 * @author DucThien
 */
public class dealDAO extends AbstractDAO<Deal, String>  {

    @Override
    protected Class getPOJOClass() {
       return Deal.class;
    }
    
    public Deal getDealById(int deal_id){
        List<Deal> listDeal = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try{ 
            String hql = "FROM Deal d WHERE d.dealId=:dealID";
            Query query = session.createQuery(hql);
            query.setInteger("dealID", deal_id);
            listDeal = query.list();
            if(listDeal.isEmpty() == true){
                return null;
            }
        }catch(HibernateException ex){
            System.err.println(ex);
        }finally{
            session.close();
        }
       
        return listDeal.get(0);
    }
    
    public List<Deal> getListDeal(int start , int limit){
        List<Deal> listDeal = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try{
            String hql = "FROM Deal d";
            Query query = session.createQuery(hql);
            query.setFirstResult(start);
            query.setMaxResults(limit);
            listDeal = query.list();
            if(listDeal.isEmpty() == true){
                return null;
            }
        }catch(HibernateException ex){
            System.err.println(ex);
        }finally{
            session.close();
        }
       
        return listDeal;    
    }
    
    public int totalDeal(){
        List<Deal> listDeal = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try{
            String hql = "FROM Deal d";
            Query query = session.createQuery(hql);
            
            listDeal = query.list();
            if(listDeal.isEmpty() == true){
                return 0;
            }
        }catch(HibernateException ex){
            System.err.println(ex);
        }finally{
            session.close();
        }
       
        return listDeal.size();    
    }
    
    public List<Deal> getListDealByProvince(int province_id , int start , int limit){
        List<Deal> listDeal = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try{
            String hql = "FROM Deal d , DealLocation dl WHERE d.dealId=dl.dealId AND dl.provinceId=:province";
            Query query = session.createQuery(hql);
            query.setString("province",String.valueOf(province_id));
            query.setFirstResult(start);
            query.setMaxResults(limit);
            listDeal = query.list();
            if(listDeal.isEmpty() == true){
                return null;
            }
        }catch(HibernateException ex){
            System.err.println(ex);
        }finally{
            session.close();
        }       
        return listDeal;
    }
    
    public List<Deal> filterDeal(String dealer_name ,String deal_name , float percent_discount , String address_dealer ,float from_cost , float to_cost , int start , int limit){
        List<Deal> listDeal = null;
        boolean flagDealerName = false, 
                flagDeal = false ,
                flagPercentDiscount = false,
                flagAddressDealer = false,
                flagFromCost = false , flagToCost = false;
        String hql = "SELECT d FROM Deal as d , Product as p , Dealer dl WHERE d.productId=p.productId AND d.dealerId=dl.dealerId ";
        if(!dealer_name.isEmpty()){
            flagDealerName = true;        
            hql += " AND trim(upper(dl.dealerName)) like :dealerName";            
        }
        if(!deal_name.isEmpty()){
            flagDeal = true;
            hql += " AND p.name like :dealName";
        }
        if(percent_discount > 0){
            flagPercentDiscount = true;
            hql += " AND d.percentDiscount=:percentDiscount ";
        }
        if(!address_dealer.isEmpty()){
            flagAddressDealer = true;
            hql += " AND dl.dealerAddress like :dealerAddress ";    
        }
        // from cost => to cost
        if(from_cost != -1 && to_cost != -1){
            flagFromCost = true;
            flagToCost = true;
            hql += " AND (d.buyCost BETWEEN :fromCost AND :toCost)";
        }else if(from_cost != -1){
            flagFromCost = true;
            hql += " AND (d.buyCost >=:fromCost)";     
        }else if(to_cost != -1){
            flagToCost = true;
            hql += " AND (d.buyCost <=:toCost)";  
        }
        
        Session session = HibernateUtil.getSessionFactory().openSession();
        try{
            Query query = session.createQuery(hql);
            if(flagDealerName == true){
                query.setString("dealerName", "%"+dealer_name.trim().toUpperCase()+"%");
            }
            if(flagDeal == true){
                query.setString("dealName", "%"+deal_name+"%");
            }
            if(flagPercentDiscount == true){
                query.setFloat("percentDiscount", percent_discount);
            }
            if(flagAddressDealer == true){
                query.setString("dealerAddress","%"+address_dealer+"%" );
            }
            if(flagFromCost == true){
                query.setFloat("fromCost", from_cost);
            }
            if(flagToCost == true){
                query.setFloat("toCost", to_cost);
            }
            if(limit > 0){
                query.setFirstResult(start);
                query.setMaxResults(limit);
            }
            
            listDeal = query.list();
            if(listDeal.isEmpty() == true){
                return null;
            } 
        }catch(HibernateException ex){
            System.err.println(ex);
        }finally{
            session.close();
        }         
        return listDeal;
    }
}
